﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class PhieuBanDAO
    {
        public string LayMaBHLonNhat() // Lay mã phieu ban lon nhat
        {
            string sql = "SELECT TOP  1, MaBH FROM PhieuBan ORDER BY MaBH DESC  ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            CTPhieuBanDTO ctpbDTO = new CTPhieuBanDTO();
            while (reader.Read())
            {
                ctpbDTO.MaCTPB = reader.GetString(0);
            }
            conn.Close();
            return ctpbDTO.MaCTPB;
        }
        public string NextID(string lastID, string prefixID)
        {
            if (lastID == null)
            {
                return prefixID + "001";  // fixwidth default
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }
        public string TaoMaBHMoi()
        {
            string LastID = LayMaBHLonNhat();
            return NextID(LastID, "BH");
        }

        public int ThemPhieuBan(PhieuBanDTO pbDTO)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO PHIEUBAN(MaBH,NgayBH,MaNV,TongTien) ";
            sql = sql + " VALUES(@MaBH,@NgayBH,@MaNV,@TongTien) ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaBH", OleDbType.VarChar);
            para.Value = pbDTO.MaBH;
            para = cmd.Parameters.Add("@NgayBH", OleDbType.DBDate);
            para.Value = pbDTO.NgayBH;
            para = cmd.Parameters.Add("@MaNV", OleDbType.VarChar);
            para.Value = pbDTO.MaNV;
            para = cmd.Parameters.Add("@TongTien", OleDbType.Double);
            para.Value = pbDTO.TongTien;
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            if (res >= 0)
                return 1; // thành công
            return 0; // không thành công
        }
        public double BaoCaoTienThu(string Nam)
        {

            string sql = "SELECT SUM(TongTien) FROM PhieuBan WHERE Year(NgayBH) = '"+Nam+"'  ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            PhieuBanDTO pbDTO = new PhieuBanDTO();
            //double tongtien;
            while (reader.Read())
            {
                pbDTO.TongTien = reader.GetDouble(0);
            }

            conn.Close();
            return pbDTO.TongTien;

        }
        public double BaoCaoTienThuThang(string Thang, string Nam)
        {

            string sql = "SELECT SUM(TongTien) FROM PhieuBan WHERE Month(NgayBH) = '"+Thang+"' and Year(NgayBH) = '"+Nam+"' ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            PhieuBanDTO pbDTO = new PhieuBanDTO();
            while (reader.Read())
            {
                if (reader.IsDBNull(0))
                    pbDTO.TongTien = 0;
                else
                    //pbDTO.TongTien = Convert.ToDouble(reader.GetString(0)); // tren acess tra ve chuoi hay la int
                    pbDTO.TongTien = reader.GetDouble(0);
            }

            conn.Close();
            return pbDTO.TongTien;

        }
        public List<PhieuBanDTO> LayThangThu()
        {
            string sql = "SELECT Distinct NgayBH FROM PhieuBan";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuBanDTO> Danhsach = new List<PhieuBanDTO>();
            while (reader.Read())
            {
                PhieuBanDTO pbDTO = new PhieuBanDTO();
                pbDTO.NgayBH = reader.GetDateTime(0);
                Danhsach.Add(pbDTO);

            }
            conn.Close();
            return Danhsach;
        }

        public List<PhieuBanDTO> TheoDoiBanHang(string Thang, string Nam)
        {
            string sql = "SELECT * FROM PHIEUBAN WHERE Month(NgayBH) = '"+Thang+"' and Year(NgayBH) = '"+Nam+"'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuBanDTO> DanhSach = new List<PhieuBanDTO>();
            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2) || reader.IsDBNull(3))
                    DanhSach = null;
                else
                {
                    PhieuBanDTO pbDTO = new PhieuBanDTO();
                    pbDTO.MaBH = reader.GetString(0);
                    pbDTO.NgayBH = reader.GetDateTime(1);
                    pbDTO.MaNV = reader.GetString(2);
                    pbDTO.TongTien = reader.GetDouble(3);
                    DanhSach.Add(pbDTO);
                }

            }
            conn.Close();
            return DanhSach;
        }
        public List<PhieuBanDTO> TheoDoiXuat(string Thang, string Nam)
        {
            string sql = "SELECT * FROM PHIEUBAN WHERE Month(NgayBH) = '" + Thang + "' and Year(NgayBH) = '" + Nam + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuBanDTO> DanhSach = new List<PhieuBanDTO>();
            while (reader.Read())
            {
                if (reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2) || reader.IsDBNull(3))
                    DanhSach = null;
                else
                {
                    PhieuBanDTO pbDTO = new PhieuBanDTO();
                    pbDTO.MaBH = reader.GetString(0);
                    pbDTO.NgayBH = reader.GetDateTime(1);
                    pbDTO.MaNV = reader.GetString(2);
                    pbDTO.TongTien = reader.GetDouble(3);
                    DanhSach.Add(pbDTO);
                }

            }
            conn.Close();
            return DanhSach;
        }
    }
}
